#include "qcalcdialog.h"
#include "ui_qcalcdialog.h"


#include <QFileDialog>
#include <QSettings>
#include <QDebug>
#include <QMessageBox>
#include <QDesktopServices>
#include <QRegularExpression>
#include <QStringListModel>


QCalcDialog::QCalcDialog(QWidget *parent) :
    QDialog(parent),
    ui(new Ui::QCalcDialog)
{
    ui->setupUi(this);
    QObject::connect(ui->btnOpenFile, SIGNAL(clicked()), this, SLOT(onOpenFile()));
    QStringList names;
    for(int i = 0; i < 26; i++) {
        names.append(QChar(i + 'A'));
    }
    QStringListModel *model = new QStringListModel(ui->iField);
    model->setStringList(names);
    ui->iField->setModel(model);

    QString fName = m_settings.value("lastLetter", "A").toString();
    ui->iField->setCurrentText(fName);
}

QCalcDialog::~QCalcDialog()
{
    delete ui;
}

void QCalcDialog::onOpenFile()
{
    QString pathLast = m_settings.value("lastOpenFile", QDir::homePath()).toString();
    QString pathNow = QFileDialog::getOpenFileName(this, tr("打开Excel文件"), pathLast);
    if(pathNow.isEmpty()) {
        return;
    }
    QFileInfo fi(pathNow);
    m_settings.setValue("lastOpenFile", pathNow);
    m_settings.setValue("lastLetter", ui->iField->currentText());
    m_settings.sync();
    QXlsx::Document doc(pathNow);
    if (!doc.isLoadPackage()){
        QMessageBox::information(this, tr("失败"), tr("打开文件失败."));
        return;
    }
    QStringList sheets = doc.sheetNames();
    int idx = ui->iField->currentIndex();
    for(int i = 0; i < sheets.length(); i++) {
        const QString& name = sheets.at(i);
        //qDebug() << name;
        QXlsx::Worksheet *sheet = static_cast<QXlsx::Worksheet*>(doc.sheet(name));
        calcArea(sheet, idx+1);
    }
    QString pathSave = QFileDialog::getSaveFileName(this, tr("保存文件"), pathNow);
    if(pathSave.isEmpty()) {
        return;
    }
    if(!doc.saveAs(pathSave)) {
        QMessageBox::information(this, tr("失败"), tr("保存文件失败"));
        return;
    }
    QDesktopServices::openUrl(QUrl::fromLocalFile(pathSave));
}

void QCalcDialog::calcArea(QXlsx::Worksheet *ws, int col)
{
    QRegularExpression areaReg("(\\d+)[\\*xX](\\d+)");
    QRegularExpression volumeReg("(\\d+)[\\*xX](\\d+)[\\*xX](\\d+)");
    int maxRow, maxCol;
    ws->getFullCells(&maxRow, &maxCol);
    for(int r = 0; r < maxRow; r++) {
        for(int c = 0; c < maxCol; c++) {
            if(c != col) {
                continue;
            }
            QVariant v = ws->read(QXlsx::CellReference(r, c));
            QString fName = fieldName(r, c);
            QString txt = v.toString();
            txt = txt.simplified();           
            //qDebug() << r << c << txt << fName;
            QString va = txt;
            QRegularExpressionMatch vmatch = volumeReg.match(va);
            if(vmatch.hasMatch()) {
                QString l = vmatch.captured(1);
                QString w = vmatch.captured(2);
                QString h = vmatch.captured(3);
                double fl = l.toDouble();
                double fw = w.toDouble();
                double fh = h.toDouble();
                int baseCol = maxCol + 1;
                ws->write(QXlsx::CellReference(r, baseCol+1), fl);
                ws->write(QXlsx::CellReference(r, baseCol+2), fw);
                ws->write(QXlsx::CellReference(r, baseCol+3), fh);
                ws->write(QXlsx::CellReference(r, baseCol+4), fl + 60);
                ws->write(QXlsx::CellReference(r, baseCol+5), fw + 60);
                ws->write(QXlsx::CellReference(r, baseCol+6), fh + 60);
                //ws->write(QXlsx::CellReference(r, baseCol+7), (fl + 60)/1000 * (fw + 60)/1000 * (fh+60) /1000);
                QString formula = QString("=%1/1000*%2/1000*%3/1000").arg(fieldName(r, baseCol+4), fieldName(r, baseCol+5), fieldName(r, baseCol+6));
                //qDebug() << formula;
                ws->write(QXlsx::CellReference(r, baseCol+7), formula);
                continue;
            }
            QRegularExpressionMatch amatch = areaReg.match(va);
            if(amatch.hasMatch()) {
                QString w = amatch.captured(1);
                QString h = amatch.captured(2);
                double fw = w.toDouble();
                double fh = h.toDouble();
                int baseCol = maxCol + 1;
                ws->write(QXlsx::CellReference(r, baseCol+1), fw);
                ws->write(QXlsx::CellReference(r, baseCol+2), fh);
                ws->write(QXlsx::CellReference(r, baseCol+4), fw + 60);
                ws->write(QXlsx::CellReference(r, baseCol+5), fh + 60);
                //ws->write(QXlsx::CellReference(r, baseCol+7), (fw + 60)/1000 * (fh+60)/1000);
                QString formula = QString("=%1/1000*%2/1000").arg(fieldName(r, baseCol+4), fieldName(r, baseCol+5));
                //qDebug() << formula;
                ws->write(QXlsx::CellReference(r, baseCol+7), formula);
            }

        }
    }
}

QString QCalcDialog::fieldName(int r, int c) const
{
    QString name;
    c -= 1;
    while(c >= 0) {
        int i = c / 26;
        if(i > 0) {
            name.append(QChar(i + 'A' - 1));
            c %= 26;
            continue;
        }
        name.append(QChar(c + 'A'));
        break;
    }
    name.append(QString::number(r));
    return name;
}
